﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data.OracleClient;
using System.Data;
using System.Collections;

namespace CardLogistics.DAL
{

    public abstract class DbHelperSQL
    {
        /// <summary>
        /// 从连接字符串获取数据库服务器地址
        /// </summary>
        public static readonly string ConnectionString = ConfigurationManager.AppSettings["OraConnString"].ToString();

        /// <summary>
        /// 执行sql语句返回受影响的行数
        /// </summary>
        /// <param name="SQLString">要执行的SQL语句</param>
        /// <returns>返回受影响的行数</returns>
        public static int ExecuteSql(string SQLString)
        {
            int rows = 0;

            using (OracleConnection connection = new OracleConnection(ConnectionString))
            {
                using (OracleCommand cmd = new OracleCommand(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        OracleTransaction tx = connection.BeginTransaction();
                        cmd.Transaction = tx;

                        rows = cmd.ExecuteNonQuery();
                        tx.Commit();
                        connection.Close();
                        return rows;
                    }
                    catch (System.Data.OracleClient.OracleException E)
                    {
                        connection.Close();
                        throw new Exception(E.Message);
                    }
                }
            }
        }
        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="ProcedureName"></param>
        /// <param name="cmdParms"></param>
        /// <returns></returns>
        public static int ExecuteOracleNonQuery(string ProcedureName, params OracleParameter[] cmdParms)
        {
            OracleConnection conn = new OracleConnection(ConnectionString);
            if (conn.State != ConnectionState.Open)
                conn.Open();
            OracleCommand cmd = new OracleCommand();
            cmd.Connection = conn;
            cmd.CommandText = ProcedureName;
            cmd.CommandType = CommandType.StoredProcedure;

            if (cmdParms != null)
            {
                foreach (OracleParameter parm in cmdParms)
                {
                    if (parm.Value == null)
                        parm.Value = DBNull.Value;
                    cmd.Parameters.Add(parm);
                }
            }
            return cmd.ExecuteNonQuery();
        }

        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">多条SQL语句</param>
        public static int ExecuteSqlTran(ArrayList ProcedureList)
        {
            int val = 0;
            using (OracleConnection conn = new OracleConnection(ConnectionString))
            {
                conn.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.Connection = conn;
                OracleTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    for (int n = 0; n < ProcedureList.Count; n++)
                    {
                        string strsql = ProcedureList[n].ToString();
                        if (strsql.Trim().Length > 1)
                        {
                            cmd.CommandText = strsql;
                            val += cmd.ExecuteNonQuery();
                        }
                    }
                    tx.Commit();
                }
                catch (System.Data.OracleClient.OracleException E)
                {
                    tx.Rollback();
                    throw new Exception(E.Message);
                }
                finally
                {
                    if (conn.State != ConnectionState.Closed)
                    {
                        conn.Close();
                    }
                }
            }
            return val;
        }
        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">多条SQL语句</param>		
        public static int ExecuteSqlTran(List<String> SQLStringList)
        {
            int val = 0;
            using (OracleConnection conn = new OracleConnection(ConnectionString))
            {
                conn.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.Connection = conn;
                OracleTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    foreach (string sql in SQLStringList)
                    {
                        if (!String.IsNullOrEmpty(sql))
                        {
                            cmd.CommandText = sql;
                            val += cmd.ExecuteNonQuery();

                        }
                    }
                    tx.Commit();
                }
                catch (System.Data.OracleClient.OracleException E)
                {
                    tx.Rollback();
                    throw new Exception(E.Message);
                }
                finally
                {
                    if (conn.State != ConnectionState.Closed)
                    {
                        conn.Close();
                    }
                }
            }
            return val;
        }
        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">多条SQL语句</param>		
        public static int ExecuteSqlTran(string[] SQLStringList)
        {
            int val = 0;
            using (OracleConnection conn = new OracleConnection(ConnectionString))
            {
                conn.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.Connection = conn;
                OracleTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    for (int i = 0; i < SQLStringList.Length; i++)
                    {

                        if (!string.IsNullOrEmpty(SQLStringList[i].ToString()))
                        {
                            cmd.CommandText = SQLStringList[i].ToString();
                            val += cmd.ExecuteNonQuery();
                        }
                    }
                    tx.Commit();
                }
                catch (System.Data.OracleClient.OracleException E)
                {
                    tx.Rollback();
                    throw new Exception(E.Message);
                }
                finally
                {
                    if (conn.State != ConnectionState.Closed)
                    {
                        conn.Close();
                    }
                }
            }
            return val;
        }
        /// <param name="cmdText"></param>
        /// <param name="cmdParms"></param>
        /// <returns></returns>
        public static int ExecuteOracleNonQuery(CommandType cmdType, out String rowid, string cmdText, params OracleParameter[] cmdParms)
        {
            // Create a new Oracle command 鍒涘缓涓€涓柊鐨凮racle鍛戒护
            OracleCommand cmd = new OracleCommand();

            //Create a connection 鍒涘缓鏁版嵁搴撹繛鎺?
            using (OracleConnection conn = new OracleConnection(ConnectionString))
            {
                OracleString vrowid;
                //Prepare the command 
                PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);

                //Execute the command 鎵ц鍛戒护
                int num = cmd.ExecuteOracleNonQuery(out vrowid);
                cmd.Parameters.Clear();
                rowid = vrowid.ToString();
                return num;
            }
        }
        /// <summary>
        /// 寮哄埗瑁呮崲
        /// </summary>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="cmdParms"></param>
        /// <returns></returns>
        public static object ExecuteOracleScalar(CommandType cmdType, string cmdText, params OracleParameter[] cmdParms)
        {
            OracleCommand cmd = new OracleCommand();

            using (OracleConnection conn = new OracleConnection(ConnectionString))
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
                object val = cmd.ExecuteOracleScalar();
                cmd.Parameters.Clear();
                return val;
            }
        }
        /// <summary>
        /// 鎵ц涓嶅寘鍚玸elect鍛戒护鐨勬暟鎹簱鏌ヨ Execute a database query which does not include a select
        /// </summary>
        /// <param name="cmdType">鍛藉悕绫诲瀷锛屽瓨鍌ㄨ繃绋嬫垨SQL璇彞 Command type either stored procedure or SQL</param>
        /// <param name="cmdText">瑕佹墽琛岀殑SQL鍛戒护 Acutall SQL Command</param>
        /// <param name="cmdParms">瑕佺粦瀹氬埌鍛戒护鐨勫弬鏁版暟缁?Parameters to bind to the command</param>
        /// <returns>鍙桽QL璇彞褰卞搷鐨勮褰曡鏁?/returns>
        public static int ExecuteNonQuery(CommandType cmdType, string cmdText, params OracleParameter[] cmdParms)
        {

            // Create a new Oracle command 鍒涘缓涓€涓柊鐨凮racle鍛戒护
            OracleCommand cmd = new OracleCommand();

            //Create a connection 鍒涘缓鏁版嵁搴撹繛鎺?
            using (OracleConnection conn = new OracleConnection(ConnectionString))
            {

                //Prepare the command 
                PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);

                //Execute the command 鎵ц鍛戒护
                int val = 0;
                try
                {
                    val = cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    throw ex;
                }

                cmd.Parameters.Clear();
                return val;
            }
        }

        /// <summary>
        /// Execute an OracleCommand (that returns no resultset) against an existing database transaction 
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "PublishOrders", new OracleParameter(":prodid", 24));
        /// </remarks>
        /// <param name="trans">an existing database transaction</param>
        /// <param name="cmdType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="cmdText">the stored procedure name or PL/SQL command</param>
        /// <param name="cmdParms">an array of OracleParamters used to execute the command</param>
        /// <returns>an int representing the number of rows affected by the command</returns>
        public static int ExecuteNonQuery(OracleTransaction trans, CommandType cmdType, string cmdText, params OracleParameter[] cmdParms)
        {
            OracleCommand cmd = new OracleCommand();
            PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms);
            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return val;
        }

        /// <summary>
        /// Execute an OracleCommand (that returns no resultset) against an existing database connection 
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new OracleParameter(":prodid", 24));
        /// </remarks>
        /// <param name="conn">宸插瓨鍦ㄧ殑鏁版嵁搴撹繛鎺?/param>
        /// <param name="cmdType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="cmdText">瀛樺偍杩囩▼鍚嶇О鎴朠L/SQL璇彞</param>
        /// <param name="cmdParms">an array of OracleParamters used to execute the command</param>
        /// <returns>an int representing the number of rows affected by the command</returns>
        public static int ExecuteNonQuery(OracleConnection conn, CommandType cmdType, string cmdText, params OracleParameter[] cmdParms)
        {
            OracleCommand cmd = new OracleCommand();
            PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return val;
        }

        /// <summary>
        /// Execute a select query that will return a result set
        /// </summary>
        //// <param name="cmdType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="cmdText">the stored procedure name or PL/SQL command</param>
        /// <param name="cmdParms">an array of OracleParamters used to execute the command</param>
        /// <returns></returns>
        public static OracleDataReader ExecuteReader(CommandType cmdType, string cmdText, params OracleParameter[] cmdParms)
        {

            //Create the command and connection
            OracleCommand cmd = new OracleCommand();
            OracleConnection conn = new OracleConnection(ConnectionString);

            //			try {
            //Prepare the command to execute
            PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);

            //Execute the query, stating that the connection should close when the resulting datareader has been read
            OracleDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection | CommandBehavior.KeyInfo);
            cmd.Parameters.Clear();
            return rdr;

            //			}catch (Exception e) {
            //
            //				//If an error occurs close the connection as the reader will not be used and we expect it to close the connection
            //				conn.Close();
            //				throw e;
            //			}
        }

        /// <summary>
        /// Execute Oracle Stored procedure
        /// </summary>
        /// <param name="spName"> Name of the Stored procedure to be execute</param>
        /// <param name="cmdParms">Parameter collection</param>
        /// <returns></returns>
        public static OracleDataReader ExecuteReader(string spName, params OracleParameter[] cmdParms)
        {
            return ExecuteReader(CommandType.StoredProcedure, spName, cmdParms);
        }

        /// <summary>
        /// Execute an OracleCommand that returns the first column of the first record against the database specified in the connection string 
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new OracleParameter(":prodid", 24));
        /// </remarks>
        /// <param name="cmdType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="cmdText">the stored procedure name or PL/SQL command</param>
        /// <param name="cmdParms">an array of OracleParamters used to execute the command</param>
        /// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>
        public static object ExecuteScalar(CommandType cmdType, string cmdText, params OracleParameter[] cmdParms)
        {
            OracleCommand cmd = new OracleCommand();

            using (OracleConnection conn = new OracleConnection(ConnectionString))
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
                object val = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return val;
            }
        }

        /// <summary>
        /// 鐢ㄤ簬鎵ц瀛樺偍杩囩▼
        /// </summary>
        /// <param name="cmdText"></param>
        /// <param name="cmdParms"></param>
        /// <returns></returns>
        public static object ExecuteScalar(string cmdText, params OracleParameter[] cmdParms)
        {
            return ExecuteScalar(CommandType.StoredProcedure, cmdText, cmdParms);
        }

        /// <summary>
        /// 鐢ㄤ簬鎵ц鏃犲弬鏁扮殑SQL璇彞鎴栧瓨鍌ㄨ繃绋?
        /// </summary>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <returns></returns>
        public static object ExecuteScalar(CommandType cmdType, string cmdText)
        {
            return ExecuteScalar(cmdType, cmdText, null);
        }

        /// <summary>
        /// Execute an OracleCommand that returns the first column of the first record against an existing database connection 
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  Object obj = ExecuteScalar(conn, CommandType.StoredProcedure, "PublishOrders", new OracleParameter(":prodid", 24));
        /// </remarks>
        /// <param name="conn">an existing database connection</param>
        /// <param name="cmdType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="cmdText">the stored procedure name or PL/SQL command</param>
        /// <param name="cmdParms">an array of OracleParamters used to execute the command</param>
        /// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>
        public static object ExecuteScalar(OracleConnection conn, CommandType cmdType, string cmdText, params OracleParameter[] cmdParms)
        {

            OracleCommand cmd = new OracleCommand();

            PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
            object val = cmd.ExecuteScalar();
            cmd.Parameters.Clear();
            return val;
        }


        /// <summary>
        /// Internal function to prepare a command for execution by the database
        /// </summary>
        /// <param name="cmd">Existing command object</param>
        /// <param name="conn">Database connection object</param>
        /// <param name="trans">Optional transaction object</param>
        /// <param name="cmdType">Command type, e.g. stored procedure</param>
        /// <param name="cmdText">Command test</param>
        /// <param name="cmdParms">Parameters for the command</param>
        private static void PrepareCommand(OracleCommand cmd, OracleConnection conn, OracleTransaction trans, CommandType cmdType, string cmdText, OracleParameter[] cmdParms)
        {

            //Open the connection if required
            if (conn.State != ConnectionState.Open)
                conn.Open();

            //Set up the command
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            cmd.CommandType = cmdType;

            //Bind it to the transaction if it exists
            if (trans != null)
                cmd.Transaction = trans;

            // Bind the parameters passed in
            if (cmdParms != null)
            {
                foreach (OracleParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }

        /// <summary>
        /// 璁剧疆OracleParameter锛屽鏋滃弬鏁版槸Int32銆丏ateTime銆乨ouble銆乨ecimal绛夌被鍨嬪苟涓斿搴旂殑鍊兼槸鍚勭被鍨嬬殑MinValue锛?
        /// 鍒欏皢鍙傛暟鍊艰涓篋BNull.Value锛屽拰浠庢暟鎹簱涓彇鍊兼椂鐩稿搴旓紙鑻ュ搴旂殑鏁版嵁绫诲瀷鐨勫€间负DBNull锛屽垯璁句负鍏禡inValue锛?
        /// Webdiyer 2005-12-28
        /// </summary>
        /// <param name="pName"></param>
        /// <param name="pValue"></param>
        /// <returns></returns>
        public static OracleParameter SetParam(string pName, object pValue)
        {

            //濡傛灉pValue涓簄ull锛屽垯鐩存帴杩斿洖 2004-12-6
            if (pValue == null)
                return new OracleParameter(pName, DBNull.Value);
            switch (Type.GetTypeCode(pValue.GetType()))
            {
                case TypeCode.String:
                    string tempStr = (string)pValue;
                    if (tempStr == null || tempStr.Trim().Length == 0)
                        return new OracleParameter(pName, DBNull.Value);
                    return new OracleParameter(pName, tempStr);
                case TypeCode.DateTime:
                    DateTime tempdt = (DateTime)pValue;
                    if (tempdt == DateTime.MinValue)
                        return new OracleParameter(pName, DBNull.Value);
                    return new OracleParameter(pName, tempdt);
                case TypeCode.Int32:
                    int tempnt = (int)pValue;
                    if (tempnt == int.MinValue)
                        return new OracleParameter(pName, DBNull.Value);
                    return new OracleParameter(pName, tempnt);
                case TypeCode.Double:
                    Double tempft = (Double)pValue;
                    if (tempft == double.MinValue)
                        return new OracleParameter(pName, DBNull.Value);
                    return new OracleParameter(pName, tempft);
                case TypeCode.Decimal:
                    decimal d = (decimal)pValue;
                    if (d == decimal.MinValue)
                        return new OracleParameter(pName, DBNull.Value);
                    return new OracleParameter(pName, d);
                default:
                    return new OracleParameter(pName, pValue);
            }
        }


        public static OracleParameter SetOutParam(string pName, OracleType otype, int size)
        {
            OracleParameter oprm = new OracleParameter(pName, otype, size);
            oprm.Direction = ParameterDirection.Output;
            return oprm;
        }

        /// <summary>
        /// 执行存储过程,返回数据集DataTable
        /// </summary>
        /// <param name="cmdType"></param>
        /// <param name="cmd"></param>
        /// <param name="oParams"></param>
        /// <returns></returns>
        public static DataTable FillDataTable(CommandType cmdType, string spName, params OracleParameter[] oParams)
        {
            using (OracleConnection conn = new OracleConnection(ConnectionString))
            {
                OracleDataAdapter adapter = new OracleDataAdapter(spName, conn);
                adapter.SelectCommand.CommandType = cmdType;
                if (oParams != null && oParams.Length > 0)
                {
                    foreach (OracleParameter prm in oParams)
                    {
                        adapter.SelectCommand.Parameters.Add(prm);
                    }
                }
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                return ds.Tables[0];
            }
        }

        /// <summary>
        /// 通过sql语句查询结果集
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="oParams">参数</param>
        /// <returns>返回查询的DataSet</returns>
        public static DataSet FillDataSet(string sql, params OracleParameter[] oParams)
        {
            DataSet ds = new DataSet();
            using (OracleConnection conn = new OracleConnection(ConnectionString))
            {
                OracleDataAdapter adapter = new OracleDataAdapter(sql, conn);
                if (oParams != null && oParams.Length > 0)
                {
                    foreach (OracleParameter prm in oParams)
                    {
                        adapter.SelectCommand.Parameters.Add(prm);
                    }
                }
                adapter.Fill(ds);
                return ds;
            }
        }

        public static DataSet FillDataSetByPRO(string sql, params OracleParameter[] oParams)
        {
            using (OracleConnection conn = new OracleConnection(ConnectionString))
            {
                OracleDataAdapter adapter = new OracleDataAdapter(sql, conn);
                adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                if (oParams != null && oParams.Length > 0)
                {
                    foreach (OracleParameter prm in oParams)
                    {
                        adapter.SelectCommand.Parameters.Add(prm);
                    }
                }
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                return ds;
            }
        }

        /// <summary>
        /// 根据SQL语句查询返回结果的DataTable
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns>返回查询结果的DataTable</returns>
        public static DataTable GetQueryResult(string sql)
        {
            DataTable tbl = new DataTable();
            OracleConnection conn = null;
            conn = new OracleConnection(ConnectionString);
            using (OracleDataAdapter adapter = new OracleDataAdapter(sql, conn))
            {
                adapter.Fill(tbl);
            }
            return tbl;
        }

        /// <summary>
        /// 执行存储过程，返回输出参数的值
        /// </summary>
        /// <param name="ProcedureName">过程名称</param>
        /// <param name="cmdParms">过程参数</param>
        /// <param name="OutParameter">传出参数</param>
        /// <returns>传出参数值</returns>
        public static object RunProcedure(string ProcedureName, OracleParameter[] cmdParms, OracleParameter OutParameter)
        {
            using (OracleConnection conn = new OracleConnection(ConnectionString))
            {
                if (conn.State != ConnectionState.Open)
                    conn.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.Connection = conn;
                cmd.CommandText = ProcedureName;
                cmd.CommandType = CommandType.StoredProcedure;

                if (cmdParms != null)
                {
                    foreach (OracleParameter parm in cmdParms)
                    {
                        if (parm.Direction == ParameterDirection.Input && parm.Value == null)
                            parm.Value = DBNull.Value;
                        cmd.Parameters.Add(parm);
                    }
                }
                cmd.ExecuteNonQuery();
                return cmd.Parameters[OutParameter.ParameterName].Value;//得到输出参数的值
            }
        }

        #region 执行分页存储过程,带参数传递,返回DataSet
        /// <summary>
        /// 执行分页存储过程,带参数传递,返回DataSet
        /// </summary>
        /// <param name="strSQL">查询语句</param>
        /// <returns>DataSet</returns>
        public static DataSet RunProcedureByPager(string strSQL, params OracleParameter[] cmdParms)
        {
            using (OracleConnection connection = new OracleConnection(ConnectionString))
            {
                using (OracleCommand cmd = new OracleCommand())
                {
                    using (OracleDataAdapter da = new OracleDataAdapter())
                    {
                        try
                        {
                            PrepareCommand(cmd, connection, null, strSQL, cmdParms, CommandType.StoredProcedure);
                            DataSet ds = new DataSet();
                            da.SelectCommand = cmd;
                            da.Fill(ds);
                            cmd.Parameters.Clear();
                            return ds;
                        }
                        catch (System.Data.OracleClient.OracleException ex)
                        {
                            throw new Exception(ex.Message);
                        }
                        finally
                        {
                            da.Dispose();
                            cmd.Dispose();
                            connection.Close();
                            connection.Dispose();
                        }
                    }
                }
            }
        }
        #endregion

        #region 执行带参数的SQL语句,连接未关闭,需要在调用该方法是去关闭
        /// <summary>
        /// 执行带参数的SQL语句,连接未关闭,需要在调用该方法是去关闭
        /// </summary>
        /// <param name="cmd">Command命令</param>
        /// <param name="conn">Connection命令</param>
        /// <param name="trans">事务命令,如果不需要添加事务可以传递值为nulls</param>
        /// <param name="cmdText">sql语句</param>
        /// <param name="cmdParms">参数集合,不需要时传递值为null</param>
        /// <param name="cmdType">SQL语句的类型,如:Text,StoredProcedure等</param>
        public static void PrepareCommand(OracleCommand cmd, OracleConnection conn, OracleTransaction trans, string cmdText, OracleParameter[] cmdParms, CommandType cmdType)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandType = cmdType;
            if (cmdParms != null)
            {
                foreach (OracleParameter parm in cmdParms)
                {
                    if (parm.Value == null || parm.Value.ToString() == "")
                    {
                        parm.Value = DBNull.Value;//# ORA-01008: 并非所有变量都已绑定  当OracleParameter某个值为null时，程序报错。
                    }
                    cmd.Parameters.Add(parm);
                }
            }
        }
        #endregion
        #region 卡明细操作
        /// <summary>
        /// 卡明细流水操作
        /// </summary>
        /// <param name="startNO">开始卡号</param>
        /// <param name="EndNO">结束卡号</param>
        /// <param name="noinNO">不包含卡号</param>
        /// <param name="rkId">公用参数(单号ID)，传值不同</param>
        /// <param name="doType">操作类型0或-1=白卡入库 1=白卡出库 2=半成品入库 3=制卡坏卡 4=成品出库 5=成品发放 6=未售问题卡 7=未售问题卡返厂 8=报废入库 9=调拨入库 10=调拨出库</param>
        /// <param name="doDate">操作时间</param>
        /// <param name="doDate">执行次数 0表示第一次执行</param>
        public static void CardRecord(Int32 startNO, Int32 EndNO, string noinNO, string Id, int doType, DateTime? doDate,int i)
        {
            OracleParameter[] parCard ={
                                new OracleParameter("start_value",OracleType.Number),//开始卡号
                                new OracleParameter("end_value",OracleType.Number),//结束卡号
                                new OracleParameter("noin_value",OracleType.VarChar,1000),//不包含卡号
                                new OracleParameter("price_value",OracleType.Number),//单价
                                new OracleParameter("id_value",OracleType.VarChar,50),//公用参数(单号ID)，传值不同
                                new OracleParameter("dodate_value",OracleType.DateTime),//入库时间
                                new OracleParameter("dotype_value",OracleType.Number),//操作类型
                                new OracleParameter("donum_value",OracleType.Number),//执行次数 0表示第一次执行
                                new OracleParameter("isec_value",OracleType.Number)};//是否是二次利用入库 0 否 1是
            parCard[0].Value = startNO;
            parCard[1].Value = EndNO;
            parCard[2].Value = noinNO;
            parCard[3].Value = 0;
            parCard[4].Value = Id;
            parCard[5].Value = doDate;
            parCard[6].Value = doType;
            parCard[7].Value = i;
            parCard[8].Value = 0;//是否是二次利用入库 0 否 1是 
            DbHelperSQL.ExecuteOracleNonQuery("P_CARDRECORD_Add", parCard);
        }

        /// <summary>
        /// 卡明细流水操作（添加是否是二次利用入库参数，0 否 1是）
        /// </summary>
        /// <param name="startNO">开始卡号</param>
        /// <param name="EndNO">结束卡号</param>
        /// <param name="noinNO">不包含卡号</param>
        /// <param name="rkId">公用参数(单号ID)，传值不同</param>
        /// <param name="doType">操作类型0或-1=白卡入库 1=白卡出库 2=半成品入库 3=制卡坏卡 4=成品出库 5=成品发放 6=未售问题卡 7=未售问题卡返厂 8=报废入库 9=调拨入库 10=调拨出库</param>
        /// <param name="doDate">操作时间</param>
        /// <param name="doDate">执行次数 0表示第一次执行</param>
        /// <param name="IsEC">是否是二次利用入库 0否 1是</param>
        public static void CardRecord(Int32 startNO, Int32 EndNO, string noinNO, string Id, int doType, DateTime? doDate, int i,int IsEC)
        {
            OracleParameter[] parCard ={
                                new OracleParameter("start_value",OracleType.Number),//开始卡号
                                new OracleParameter("end_value",OracleType.Number),//结束卡号
                                new OracleParameter("noin_value",OracleType.VarChar,1000),//不包含卡号
                                new OracleParameter("price_value",OracleType.Number),//单价
                                new OracleParameter("id_value",OracleType.VarChar,50),//公用参数(单号ID)，传值不同
                                new OracleParameter("dodate_value",OracleType.DateTime),//入库时间
                                new OracleParameter("dotype_value",OracleType.Number),//操作类型
                                new OracleParameter("donum_value",OracleType.Number),//执行次数 0表示第一次执行
                                new OracleParameter("isec_value",OracleType.Number)};//是否是二次利用入库 0 否 1是
            parCard[0].Value = startNO;
            parCard[1].Value = EndNO;
            parCard[2].Value = noinNO;
            parCard[3].Value = 0;
            parCard[4].Value = Id;
            parCard[5].Value = doDate;
            parCard[6].Value = doType;
            parCard[7].Value = i;
            parCard[8].Value = IsEC;
            DbHelperSQL.ExecuteOracleNonQuery("P_CARDRECORD_Add", parCard);
        }
        #endregion
    }
}
